package com.irdstudio.devops.console.dao;

import com.irdstudio.bfp.console.dao.domain.SSubsDatasource;
import com.irdstudio.devops.console.dao.domain.DboSqlLog;
import com.irdstudio.sdk.beans.core.util.KeyUtil;
import com.irdstudio.sdk.beans.core.util.TimeUtil;
import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SSubsDatasourceDao {
    @Autowired
    private DboSqlLogDao dboSqlLogDao;
    /* 执行SQL */
    public List<String> doExcetueSql(SSubsDatasource sSubsDatasource,String remark, String sql) {
        List result = new ArrayList<String>();
        PreparedStatement ps=null;
        Connection conn=null;
        DboSqlLog dboSqlLog=new DboSqlLog();
        dboSqlLog.setRecordKeyid(KeyUtil.createUUIDKey());
        dboSqlLog.setDboSqlContent(sql);
        dboSqlLog.setStartTime(TimeUtil.getCurrentDateTime());
        dboSqlLog.setDsCode(sSubsDatasource.getSubsCode());
        dboSqlLog.setDboRemarks(remark);
        long startMills= System.currentTimeMillis();
        try {
            int ColumnCount;
            //int RowCount;
            String driver = "com.mysql.jdbc.Driver";
            String url = sSubsDatasource.getDsConnStr()+"?useSSL=false&serverTimezone=UTC&allowMultiQueries=true"; //换成要连接的数据库信息
            String user = sSubsDatasource.getDsUserId();
            String password = sSubsDatasource.getDsUserPwd();
            Class.forName (driver);
            conn = (Connection) DriverManager.getConnection ( url, user, password );
            if (!conn.isClosed ()) {
                ps = conn.prepareStatement (sql);
                if(sql.toLowerCase().startsWith("select")){
                    ResultSet rs = ps.executeQuery ();
                    ResultSetMetaData rsmd = rs.getMetaData ();
                    while (rs.next ()) {
                        ColumnCount = rsmd.getColumnCount ();
                        Map<String,Object> rowData = new HashMap<String,Object>();
                        for (int i = 1; i <= ColumnCount; i++) {
                            rowData.put(rsmd.getColumnName(i),rs.getObject(i));
                        }
                        result.add(rowData);
                    }
                }else{
                    int rs = ps.executeUpdate();
                    result.add("影响条数："+rs);
                    dboSqlLog.setDboAffectedNum(rs);
                }
                long endMills = System.currentTimeMillis();
                dboSqlLog.setEndTime(TimeUtil.getCurrentDateTime());
                dboSqlLog.setCostTime(new BigDecimal(endMills - startMills).divide(BigDecimal.valueOf(1000.00)));
                dboSqlLog.setDboResult("Y");
                ps = conn.prepareStatement("INSERT INTO dbo_sql_log VALUES (?,?,?,?,?,?,?,?,?,?,?)");
                ps.setObject(1, dboSqlLog.getRecordKeyid());
                ps.setObject(2, dboSqlLog.getDboSqlContent());
                ps.setObject(3, dboSqlLog.getDboResult());
                ps.setObject(4, dboSqlLog.getDboAffectedNum());
                ps.setObject(5, dboSqlLog.getDboErrMsg());
                ps.setObject(6, dboSqlLog.getStartTime());
                ps.setObject(7, dboSqlLog.getEndTime());
                ps.setObject(8, dboSqlLog.getCostTime());
                ps.setObject(9, dboSqlLog.getOperUserid());
                ps.setObject(10,dboSqlLog.getDsCode());
                ps.setObject(11,dboSqlLog.getDboRemarks());
                ps.executeUpdate();
            }
            ps.close ();
            conn.close ();
            return result;
        } catch (ClassNotFoundException e) {
            result.add("失败原因："+e.getMessage());
            long endMills = System.currentTimeMillis();
            dboSqlLog.setEndTime(TimeUtil.getCurrentDateTime());
            dboSqlLog.setCostTime(new BigDecimal(endMills - startMills).divide(BigDecimal.valueOf(1000.00)));
            dboSqlLog.setDboErrMsg(e.getMessage());
            e.printStackTrace ();
        } catch (SQLException e) {
            result.add("失败原因："+e.getMessage());
            long endMills = System.currentTimeMillis();
            dboSqlLog.setEndTime(TimeUtil.getCurrentDateTime());
            dboSqlLog.setCostTime(new BigDecimal(endMills - startMills).divide(BigDecimal.valueOf(1000.00)));
            dboSqlLog.setDboErrMsg(e.getMessage());
            e.printStackTrace ();
        }
        dboSqlLog.setDboResult("N");
        try{
            ps = conn.prepareStatement("INSERT INTO dbo_sql_log VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        ps.setObject(1, dboSqlLog.getRecordKeyid());
        ps.setObject(2, dboSqlLog.getDboSqlContent());
        ps.setObject(3, dboSqlLog.getDboResult());
        ps.setObject(4, dboSqlLog.getDboAffectedNum());
        ps.setObject(5, dboSqlLog.getDboErrMsg());
        ps.setObject(6, dboSqlLog.getStartTime());
        ps.setObject(7, dboSqlLog.getEndTime());
        ps.setObject(8, dboSqlLog.getCostTime());
        ps.setObject(9, dboSqlLog.getOperUserid());
        ps.setObject(10,dboSqlLog.getDsCode());
        ps.setObject(11,dboSqlLog.getDboRemarks());
        ps.executeUpdate(); } catch (SQLException e) {
            e.getMessage();
        }
        return result;
    }

    /* 连接测试 */
    public String sqlConnection(SSubsDatasource sSubsDatasource) {
        String driverName = null;
        if ("05".equals(sSubsDatasource.getSubsDsType())) {
            //mysql
            driverName = "com.mysql.jdbc.Driver";
        } else if ("1".equals(sSubsDatasource.getSubsDsType())) {
            //oracle
            driverName = "oracle.jdbc.driver.OracleDriver";
        } else if ("2".equals(sSubsDatasource.getSubsDsType())) {
            //sqlserver
            driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        }
        try {
            Class.forName(driverName);
            String url=sSubsDatasource.getDsConnStr()+"?useSSL=false&serverTimezone=UTC&allowMultiQueries=true";
            Connection connection = DriverManager.getConnection(url, sSubsDatasource.getDsUserId(), sSubsDatasource.getDsUserPwd());
            connection.close();
        } catch (Exception e) {
            return "测试失败:"+e;
        }
        return "测试成功";
    }

    /* 获取数据库所有的表名 */
    public List<String> getTableName(SSubsDatasource sSubsDatasource){
        List result = new ArrayList<String>();
        PreparedStatement ps=null;
        Connection conn=null;
        String driverName = null;
        if ("05".equals(sSubsDatasource.getSubsDsType())) {
            //mysql
            driverName = "com.mysql.jdbc.Driver";
        } else if ("1".equals(sSubsDatasource.getSubsDsType())) {
            //oracle
            driverName = "oracle.jdbc.driver.OracleDriver";
        } else if ("2".equals(sSubsDatasource.getSubsDsType())) {
            //sqlserver
            driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        }try{
            int ColumnCount;
            //int RowCount;
            String url = sSubsDatasource.getDsConnStr()+"?useSSL=false&serverTimezone=UTC&allowMultiQueries=true"; //换成要连接的数据库信息
            String user = sSubsDatasource.getDsUserId();
            String password = sSubsDatasource.getDsUserPwd();
            String sql="SELECT TB.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES TB WHERE TB.TABLE_SCHEMA ='"+sSubsDatasource.getDsDbName()+"'";
            Class.forName (driverName);
            conn = (Connection) DriverManager.getConnection ( url, user, password );
            ps = conn.prepareStatement (sql);
            ResultSet rs = ps.executeQuery ();
            ResultSetMetaData rsmd = rs.getMetaData ();
            while (rs.next ()) {
                ColumnCount = rsmd.getColumnCount ();
                Map<String,Object> rowData = new HashMap<String,Object>();
                for (int i = 1; i <= ColumnCount; i++) {
                    rowData.put(rsmd.getColumnName(i),rs.getObject(i));
                }
                result.add(rowData);
            }
            return result;
        }catch(SQLException | ClassNotFoundException e){
            result.add("失败原因："+e.getMessage());
            e.printStackTrace();
            return result;
        }
    }
}
